package com.zxmseed.generateanalysis.util;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author yangchaomin
 * @date 2020/04/20 14:16
 */
@Slf4j
public class ExcelKit {

    private static final String XLS = "xls";
    private static final String XLSX = "xlsx";
    private static final String PREFIX = "sheet";
    public static final String ATTRIBUTE_SHEET = "sheet1";


    /**
     * 根据文件后缀名类型获取对应的工作簿对象
     *
     * @param inputStream 读取文件的输入流
     * @param fileType    文件后缀名类型（xls或xlsx）
     * @return 包含文件数据的工作簿对象
     * @throws IOException ioexception
     */
    public static Workbook getWorkbook(InputStream inputStream, String fileType) throws IOException {
        Workbook workbook = null;
        if (fileType.equalsIgnoreCase(XLS)) {
            workbook = new HSSFWorkbook(inputStream);
        } else /*(fileType.equalsIgnoreCase(XLSX))*/ {
            workbook = new XSSFWorkbook(inputStream);
        }
        return workbook;
    }

    /**
     * 解析Excel数据
     *
     * @param workbook Excel工作簿对象
     * @return 解析结果
     */
    public static Map<String, List<List<String>>> parseExcel(Workbook workbook) {
        System.out.println("开始解析Excel数据");
        Map<String, List<List<String>>> sheetDataMap = new HashMap<>();
        List<List<String>> dataList = null;
        List<String> rowList = null;
        int maxCellNum = 0;
        /*sheetNum < workbook.getNumberOfSheets()*/
        for (int sheetNum = 0; sheetNum < 1; sheetNum++) {
            Sheet sheet = workbook.getSheetAt(sheetNum);
            if (sheet == null) {
                continue;
            }

            String mapKey = PREFIX + (sheetNum + 1);
            dataList = new ArrayList<>();

            for (int rowStart = sheet.getFirstRowNum(), rowEnd = sheet.getLastRowNum();
                 rowStart <= rowEnd; rowStart++) {
                Row row = sheet.getRow(rowStart);
                if (null == row) {
                    continue;
                }
                rowList = new ArrayList<>(row.getLastCellNum());
                // 取第一行数据cell个数作为标准
                maxCellNum = 1;
                /*if (maxCellNum == 0) {
                    maxCellNum = row.getLastCellNum();
                }*/
                for (int cellStart = 0; cellStart < maxCellNum; cellStart++) {
                    Cell cell = row.getCell(cellStart);
                    if (cell != null) {
                        rowList.add(convertCellValueToString(cell));
                    } else {
                        //
                    }
                }
                dataList.add(rowList);
            }
            sheetDataMap.put(mapKey, dataList);
            System.out.println("标题数共：" + dataList.size() + "条");
        }
        System.out.println("解析Excel数据结束");
        return sheetDataMap;
    }

    /**
     * 将单元格内容转换为字符串
     *
     * @param cell cell
     * @return String
     */
    private static String convertCellValueToString(Cell cell) {
        if (cell == null) {
            return null;
        }
        String returnValue = null;
        switch (cell.getCellTypeEnum()) {
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    returnValue = formater.format(date);
                } else if (String.valueOf(cell.getNumericCellValue()).contains(".")) {
                    DecimalFormat df = new DecimalFormat("#");
                    returnValue = df.format(cell.getNumericCellValue());
                } else {
                    returnValue = String.valueOf(cell.getNumericCellValue());
                }
                break;
            case STRING:
                returnValue = cell.getStringCellValue();
                break;
            case BOOLEAN:
                boolean booleanValue = cell.getBooleanCellValue();
                returnValue = Boolean.toString(booleanValue);
                break;
            case FORMULA:
                // returnValue = cell.getCellFormula();
                try {
                    returnValue = String.valueOf(cell.getNumericCellValue());
                } catch (IllegalStateException e) {
                    returnValue = String.valueOf(cell.getRichStringCellValue());
                }
                break;
            case BLANK:
                break;
            case ERROR:
                break;
            default:
                break;
        }
        return returnValue;
    }

    private static String replaceNum(String text) {
        return text.replaceAll("\\d", "");
    }


}
